﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Reflection;
using System.IO;
using System.Diagnostics;
using System.Text.RegularExpressions;

namespace template_dll
{
    public partial class ChildForm : Form
    {
        public ChildForm()
        {
            InitializeComponent();
        }

        private const string namechcol = "Флаг";
        private SqlConnection conn;
        private DataTable dt_rep;
        private DataTable suppl;
        private DataTable pharm;
        private DataTable income;
        private DataTable request;
        private delegate void Deleg();
        private Deleg del;
        private Guid guid;
        //private bool reset = false;
        //private struct Dict { public int id; public string text; } 

        public static bool DisplayForm(System.Windows.Forms.Form theMDIParent)
        {
            ChildForm f = new ChildForm();
            f.MdiParent = theMDIParent;
            f.WindowState = FormWindowState.Maximized;
            f.Show();
            return true;
        }

        private string GetServ(string filename)
        {
            string str = "";
            using (StreamReader streamReader = new StreamReader(filename))
            {
                while (!streamReader.EndOfStream)
                {
                    str += streamReader.ReadLine();
                }
            }
            return str;
        }

        private void ChildForm_Load(object sender, EventArgs e)
        {
            conn = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=PharmOLAPDW;Integrated Security=True");
            dt_rep = new DataTable();
            guid = Guid.NewGuid();
            listBox1.Items.Add("Не выбрано");
            suppl = new DataTable();
            suppl.Load(ProcSel(conn, @"pas_SupplSel"));
            foreach (DataColumn dc in suppl.Columns)
            {
                if (dc.ColumnName != "Флаг") dc.ReadOnly = true;
                else dc.ReadOnly = false;
            }

            listBox2.Items.Add("Не выбрано");
            pharm = new DataTable();
            pharm.Load(ProcSel(conn, @"pas_PharmSel"));
            foreach (DataColumn dc in pharm.Columns)
            {
                if (dc.ColumnName != "Флаг") dc.ReadOnly = true;
                else dc.ReadOnly = false;
            }
            listBox3.Items.Add("Не выбрано");
            listBox4.Items.Add("Не выбрано");
            checkBox4.Checked = true;
            checkBox5.Checked = true;

            dateTimePicker1.Value = DateTime.Now.Date.AddDays(-1);
            dateTimePicker2.Value = DateTime.Now.Date.AddDays(-1);
            dateTimePicker3.Value = DateTime.Now.Date.AddDays(-1);
            dateTimePicker4.Value = DateTime.Now.Date.AddDays(-1);
            //income = new DataTable();
            //request = new DataTable();
        }

        private SqlDataReader ProcSel(SqlConnection con, string procname)
        {
            SqlCommand com = con.CreateCommand();
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = procname;
            if (con.State == ConnectionState.Closed) con.Open();
            return com.ExecuteReader();
        }

        private void StartCreateRep(object sender, EventArgs e)
        {
            if (listBox3.Items.Count > 0)
            {
                if (listBox4.Items.Count > 0)
                {
                    if (listBox1.Items[0].ToString() != "Не выбрано")
                    {
                        if (listBox2.Items[0].ToString() != "Не выбрано")
                        {
                            dataGridView1.DataSource = null;
                            dt_rep.Clear();
                            dt_rep = null;
                            dt_rep = new DataTable();
                            del = new Deleg(CreateRep);
                            this.Text += " Формируется";
                            IAsyncResult asyncRes = del.BeginInvoke(new AsyncCallback(CallBackFunc), null); //
                            panel1.Enabled = false;
                            dataGridView1.Enabled = false;
                        }
                        else MessageBox.Show("Не выбрана аптека");
                    }
                    else MessageBox.Show("Не выбран поставщик");
                }
                else MessageBox.Show("Не выбраны заявки");
            }
            else MessageBox.Show("Не выбраны поступления");
        }

        private void CallBackFunc(IAsyncResult aRes)
        {
            del.EndInvoke(aRes);
            panel1.Invoke((Func<bool>)(() => panel1.Enabled = true));
            dataGridView1.Invoke((Func<bool>)(() => dataGridView1.Enabled = true));
            this.Invoke((Func<string>)(() => this.Text = this.Text.Replace(" Формируется", "")));
            dataGridView1.Invoke((Func<object>)(() => dataGridView1.DataSource = dt_rep.DefaultView));
            dataGridView1.Invoke((Func<object>)(() => dataGridView1.Columns[dataGridView1.ColumnCount - 1].DefaultCellStyle.Format = "p"));
        }

        private void CreateRep()
        {
            if (conn.State == ConnectionState.Closed) conn.Open();
            try
            {
                SqlCommand myCommand = conn.CreateCommand();
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.CommandText = @"pas_GuidNatureCodeINS";
                myCommand.Parameters.Add("@guid", SqlDbType.UniqueIdentifier);
                myCommand.Parameters.Add("@code", SqlDbType.Int);
                myCommand.Parameters.Add("@del", SqlDbType.Bit);
                myCommand.Parameters.Add("@nature", SqlDbType.VarChar, 5);
                myCommand.Parameters.Add("@naturecomm", SqlDbType.VarChar, 50);

                if (conn.State == ConnectionState.Closed) conn.Open();

                myCommand.Parameters["@guid"].Value = guid;
                myCommand.Parameters["@code"].Value = null;
                myCommand.Parameters["@del"].Value = true;
                myCommand.Parameters["@nature"].Value = null;
                myCommand.ExecuteNonQuery();

                guid = Guid.NewGuid();

                if (suppl != null)
                    foreach (DataRow dr in suppl.Rows)
                            if (Convert.ToBoolean(dr[namechcol]) == true)
                            {
                                myCommand.Parameters["@guid"].Value = guid;
                                myCommand.Parameters["@code"].Value = Convert.ToInt32(dr["Код"]);
                                myCommand.Parameters["@del"].Value = false;
                                myCommand.Parameters["@nature"].Value = "sup";
                                myCommand.ExecuteNonQuery();
                            }

                if (pharm != null)
                    foreach (DataRow dr in pharm.Rows)
                            if (Convert.ToBoolean(dr[namechcol]) == true)
                            {
                                myCommand.Parameters["@guid"].Value = guid;
                                myCommand.Parameters["@code"].Value = Convert.ToInt32(dr["Код"]);
                                myCommand.Parameters["@del"].Value = false;
                                myCommand.Parameters["@nature"].Value = "pharm";
                                myCommand.ExecuteNonQuery();
                            }
                if(!checkBox4.Checked)
                    for (int i = 0; i < listBox4.Items.Count; i++)
                    {
                        myCommand.Parameters["@guid"].Value = guid;
                        myCommand.Parameters["@code"].Value = 0;
                        myCommand.Parameters["@del"].Value = false;
                        myCommand.Parameters["@nature"].Value = "req";
                        myCommand.Parameters["@naturecomm"].Value = listBox4.Items[i].ToString();
                        myCommand.ExecuteNonQuery();
                    }

                if(!checkBox5.Checked)
                    for (int i = 0; i < listBox3.Items.Count; i++)
                    {
                        myCommand.Parameters["@guid"].Value = guid;
                        myCommand.Parameters["@code"].Value = 0;
                        myCommand.Parameters["@del"].Value = false;
                        myCommand.Parameters["@nature"].Value = "inc";
                        myCommand.Parameters["@naturecomm"].Value = listBox3.Items[i].ToString();
                        myCommand.ExecuteNonQuery();
                    }

                dt_rep.Load(RepProc(guid, conn));

                myCommand.Parameters["@guid"].Value = guid;
                myCommand.Parameters["@code"].Value = null;
                myCommand.Parameters["@del"].Value = true;
                myCommand.Parameters["@nature"].Value = null;
                myCommand.ExecuteNonQuery();

                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                conn.Close();
            }

        }

        private SqlDataReader RepProc(Guid id, SqlConnection sqlcon)
        {
            SqlCommand sqlcom = sqlcon.CreateCommand();
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = @"pas_DAC_BadSupplier";
            sqlcom.CommandTimeout = 300;

            sqlcom.Parameters.Add("@guid", SqlDbType.UniqueIdentifier);
            sqlcom.Parameters["@guid"].Value = id;

            sqlcom.Parameters.Add("@all", SqlDbType.Bit);
            sqlcom.Parameters["@all"].Value = checkBox1.Checked;

            sqlcom.Parameters.Add("@pharm_sup", SqlDbType.Bit);
            if (radioButton1.Checked) sqlcom.Parameters["@pharm_sup"].Value = false;
            else sqlcom.Parameters["@pharm_sup"].Value = true;

            sqlcom.Parameters.Add("@incbeg", SqlDbType.Date);
            sqlcom.Parameters["@incbeg"].Value = dateTimePicker3.Value.Date;

            sqlcom.Parameters.Add("@incend", SqlDbType.Date);
            sqlcom.Parameters["@incend"].Value = dateTimePicker4.Value.Date;

            sqlcom.Parameters.Add("@reqbeg", SqlDbType.Date);
            sqlcom.Parameters["@reqbeg"].Value = dateTimePicker1.Value.Date;

            sqlcom.Parameters.Add("@reqend", SqlDbType.Date);
            sqlcom.Parameters["@reqend"].Value = dateTimePicker2.Value.Date;

            //sqlcom.Parameters.Add("@baseid", SqlDbType.Int);
            //this.Invoke((Func<object>)(() => sqlcom.Parameters["@baseid"].Value = basema[comboBox1.SelectedIndex].id));

            //sqlcom.Parameters.Add("@doctypeid", SqlDbType.Int);
            //this.Invoke((Func<object>)(() => sqlcom.Parameters["@doctypeid"].Value = basema[comboBox2.SelectedIndex].id));

            return sqlcom.ExecuteReader();
        }

        private void ExpToExcel(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.FileName = "Сравнение заявок и поступлений";
            sfd.Filter = "Файл CSV | *.csv";
            if (sfd.ShowDialog() == DialogResult.OK)
            {   
                SaveDGVToCSVfile(sfd.FileName.ToString(), dataGridView1, true);
            }
        }

        public static bool SaveDGVToCSVfile(string filename, DataGridView table, bool openInExcel)
        {
            try
            {
                StreamWriter sw = new StreamWriter(filename, false, Encoding.Unicode);

                List<int> col_n = new List<int>();
                foreach (DataGridViewColumn col in table.Columns)
                    if (col.Visible)
                    {
                        sw.Write(col.HeaderText + "\t");
                        col_n.Add(col.Index);
                    }
                sw.WriteLine();
                int x = table.RowCount;
                if (table.AllowUserToAddRows) x--;

                for (int i = 0; i < x; i++)
                {
                    for (int y = 0; y < col_n.Count; y++)
                        sw.Write(table[col_n[y], i].Value + "\t");
                    sw.Write(" \r\n");
                }
                sw.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return false;
            }

            if (openInExcel)
            {
                try
                {
                    Process newProc = Process.Start("excel.exe", "\"" + filename + "\"");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                }
            }
            return true;
        }

        private void button4_Click(object sender, EventArgs e)
        {

        }



        private void HideResult(DataGridView buffview)
        {
            if (buffview.DataSource != null)
            {
                CurrencyManager cm = (CurrencyManager)buffview.BindingContext[buffview.DataSource];
                cm.SuspendBinding();
                int select = 0;
                int filter = 0;
                foreach (DataGridViewRow r in buffview.Rows)
                {
                    r.Visible = true;
                    r.DefaultCellStyle.BackColor = SystemColors.ControlLightLight;

                    //if (!Regex.Match(Convert.ToString(buffview[buffview.SortedColumn.Name, r.Index].Value), textBox1.Text, RegexOptions.IgnoreCase).Success)
                    //    r.Visible = false;

                    //if (checkBox1.Checked)
                    //    if (!Convert.ToBoolean(dataGridView1[namechcol, r.Index].Value)) r.Visible = false;

                    if (Convert.ToBoolean(buffview[namechcol, r.Index].Value))
                    {
                        select++;
                        r.DefaultCellStyle.BackColor = Color.Red;
                    }
                    if (r.Visible) filter++;
                }
                cm.ResumeBinding();
            }
        }



        private void button3_Click(object sender, EventArgs e)
        {
            int fl = 0;
            listBox1.Items.Clear();
            suppl = FiltFormShow("Поставщики", namechcol, "Поставщик", suppl);
            fl = 0;
            foreach (DataRow dr in suppl.Rows)
            {
                if (Convert.ToBoolean(dr["Флаг"]))
                {
                    fl++;
                    listBox1.Items.Add(dr["Поставщик"].ToString());
                }
            }
            if (fl == 0) checkBox2.Checked = true;
            //listBox1.Items.Add("Не выбрано");
            if (!checkBox4.Checked) checkBox4.Checked = true;
            if (!checkBox5.Checked) checkBox5.Checked = true;
        }

        public DataTable FiltFormShow(string formname, string flname, string sort, DataTable dt)
        {
            PharmFilter.PharmFilter f = new PharmFilter.PharmFilter();
            f.Name = formname;
            f.dataGridView1.DataSource = dt.DefaultView;
            f.namechcol = flname;
            f.sortcoumn = sort;
            f.ShowDialog();
            f.Close();
            f = null;
            return dt;
        }

        private void button4_Click_1(object sender, EventArgs e)
        {
            int fl = 0;
            listBox2.Items.Clear();
            pharm = FiltFormShow("Аптеки", namechcol, "Аптека", pharm);
            fl = 0;
            foreach (DataRow dr in pharm.Rows)
            {
                if (Convert.ToBoolean(dr["Флаг"]))
                {
                    fl++;
                    listBox2.Items.Add(dr["Аптека"].ToString());
                }
            }
            if (fl == 0) checkBox3.Checked = true;//listBox2.Items.Add("Не выбрано");
            if (!checkBox4.Checked) checkBox4.Checked = true;
            if (!checkBox5.Checked) checkBox5.Checked = true;
        }

        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void button5_Click(object sender, EventArgs e)
        {
            checkBox4.Checked = false;
            if (listBox1.Items.Count > 0 && listBox1.Items[0].ToString() != "Не выбрано" && listBox2.Items.Count > 0 && listBox2.Items[0].ToString() != "Не выбрано")
            {
                income = null;
                income = new DataTable();
                income = SelDocs(income, "pas_DocsSel", 91, dateTimePicker1, dateTimePicker2);
                income = FiltFormShow("Заявки", namechcol, "Документ", income);
                if(income.Rows.Count > 0) listBox4.Items.Clear();
                int fl = 0;
                foreach (DataRow dr in income.Rows)
                {
                    if (Convert.ToBoolean(dr["Флаг"]))
                    {
                        fl++;
                        listBox4.Items.Add(dr["Документ"].ToString() + " от " + Convert.ToDateTime(dr["Дата документа"].ToString()).Date.ToString().Substring(0,10));
                    }
                }
                if (fl == 0) checkBox4.Checked = true;
            }
            else
            {
                if (listBox1.Items.Count > 0 && listBox1.Items[0].ToString() == "Не выбрано") MessageBox.Show("Не выбран поставщик");
                if (listBox2.Items.Count > 0 && listBox2.Items[0].ToString() == "Не выбрано") MessageBox.Show("Не выбрана аптека");
                checkBox4.Checked = true;
            }
        }

        private DataTable SelDocs(DataTable dt, string proc, int doctype, DateTimePicker beg, DateTimePicker end)
        {
            if (conn.State == ConnectionState.Closed) conn.Open();
            try
            {
                SqlCommand myCommand = conn.CreateCommand();
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.CommandText = @"pas_GuidNatureCodeINS";
                myCommand.Parameters.Add("@guid", SqlDbType.UniqueIdentifier);
                myCommand.Parameters.Add("@code", SqlDbType.Int);
                myCommand.Parameters.Add("@del", SqlDbType.Bit);
                myCommand.Parameters.Add("@nature", SqlDbType.VarChar, 5);

                if (conn.State == ConnectionState.Closed) conn.Open();

                myCommand.Parameters["@guid"].Value = guid;
                myCommand.Parameters["@code"].Value = null;
                myCommand.Parameters["@del"].Value = true;
                myCommand.Parameters["@nature"].Value = null;
                myCommand.ExecuteNonQuery();

                guid = Guid.NewGuid();

                if (suppl != null)
                    foreach (DataRow dr in suppl.Rows)
                        if (Convert.ToBoolean(dr[namechcol]) == true)
                        {
                            myCommand.Parameters["@guid"].Value = guid;
                            myCommand.Parameters["@code"].Value = Convert.ToInt32(dr["Код"]); ;
                            myCommand.Parameters["@del"].Value = false;
                            myCommand.Parameters["@nature"].Value = "sup";
                            myCommand.ExecuteNonQuery();
                        }

                if (pharm != null)
                    foreach (DataRow dr in pharm.Rows)
                        if (Convert.ToBoolean(dr[namechcol]) == true)
                        {
                            myCommand.Parameters["@guid"].Value = guid;
                            myCommand.Parameters["@code"].Value = Convert.ToInt32(dr["Код"]); ;
                            myCommand.Parameters["@del"].Value = false;
                            myCommand.Parameters["@nature"].Value = "pharm";
                            myCommand.ExecuteNonQuery();
                        }


                dt.Load(RepProc(guid, conn, proc, doctype, beg,end));
                foreach (DataColumn dc in dt.Columns)
                {
                    if (dc.ColumnName != "Флаг") dc.ReadOnly = true;
                    else dc.ReadOnly = false;
                }
                myCommand.Parameters["@guid"].Value = guid;
                myCommand.Parameters["@code"].Value = null;
                myCommand.Parameters["@del"].Value = true;
                myCommand.Parameters["@nature"].Value = null;
                myCommand.ExecuteNonQuery();

                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                conn.Close();
            }
            return dt;

        }

        private SqlDataReader RepProc(Guid id, SqlConnection sqlcon, string proc, int doctype, DateTimePicker beg, DateTimePicker end)
        {
            SqlCommand sqlcom = sqlcon.CreateCommand();
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = proc;
            sqlcom.CommandTimeout = 300;

            sqlcom.Parameters.Add("@guid", SqlDbType.UniqueIdentifier);
            sqlcom.Parameters["@guid"].Value = id;

            sqlcom.Parameters.Add("@doctypeid", SqlDbType.Int);
            sqlcom.Parameters["@doctypeid"].Value = doctype;

            sqlcom.Parameters.Add("@dt_beg", SqlDbType.Date);
            sqlcom.Parameters["@dt_beg"].Value = beg.Value.Date;

            sqlcom.Parameters.Add("@dt_end", SqlDbType.Date);
            sqlcom.Parameters["@dt_end"].Value = end.Value.Date;

            return sqlcom.ExecuteReader();
        }

        private void button6_Click(object sender, EventArgs e)
        {
            checkBox5.Checked = false;
            if (listBox1.Items.Count > 0 && listBox1.Items[0].ToString() != "Не выбрано" && listBox2.Items.Count > 0 && listBox2.Items[0].ToString() != "Не выбрано")
            {         
                request = null;
                request = new DataTable();
                request = SelDocs(request, "pas_DocsSel", 1, dateTimePicker3, dateTimePicker4);
                request = FiltFormShow("Поступления", namechcol, "Документ", request);
                if (request.Rows.Count > 0) listBox3.Items.Clear();
                int fl = 0;
                foreach (DataRow dr in request.Rows)
                {
                    if (Convert.ToBoolean(dr["Флаг"]))
                    {
                        fl++;
                        listBox3.Items.Add(dr["Документ"].ToString() + " от " + Convert.ToDateTime(dr["Дата документа"].ToString()).Date.ToString().Substring(0,10));
                    }
                }
                if (fl == 0) checkBox5.Checked = true;
            }
            else
            {
                if (listBox1.Items.Count > 0 && listBox1.Items[0].ToString() == "Не выбрано") MessageBox.Show("Не выбран поставщик");
                if (listBox2.Items.Count > 0 && listBox2.Items[0].ToString() == "Не выбрано") MessageBox.Show("Не выбрана аптека");
                checkBox5.Checked = true;
            }
        }

        private void dataGridView1_RowPrePaint(object sender, DataGridViewRowPrePaintEventArgs e)
        {
            if ((int)dataGridView1[0, e.RowIndex].Value == 0) 
            {
                if(dataGridView1[4, e.RowIndex].Value.ToString() == "   Итого по всем поставщикам" || dataGridView1[3, e.RowIndex].Value.ToString() == "  Итого по всем аптекам") dataGridView1.Rows[e.RowIndex].DefaultCellStyle.BackColor = Color.LightGreen;
                else dataGridView1.Rows[e.RowIndex].DefaultCellStyle.BackColor = Color.SkyBlue;
            }
            if (dataGridView1[4, e.RowIndex].Value.ToString() == " Итого по поставщику" || dataGridView1[3, e.RowIndex].Value.ToString() == " Итого по аптеке") dataGridView1.Rows[e.RowIndex].DefaultCellStyle.BackColor = Color.Pink;
        }

        private void checkBox2_CheckedChanged(object sender, EventArgs e)
        {
            listBox1.Items.Clear();
            if (checkBox2.Checked) listBox1.Items.Add("Все");
            else listBox1.Items.Add("Не выбрано");
            foreach (DataRow dr in suppl.Rows) dr["Флаг"] = checkBox2.Checked;
        }

        private void checkBox3_CheckedChanged(object sender, EventArgs e)
        {
            listBox2.Items.Clear();
            if (checkBox3.Checked) listBox2.Items.Add("Все");
            else listBox2.Items.Add("Не выбрано");
            foreach (DataRow dr in pharm.Rows) dr["Флаг"] = checkBox3.Checked;
        }

        private void checkBox4_CheckedChanged(object sender, EventArgs e)
        {
            listBox4.Items.Clear();
            if (checkBox4.Checked) listBox4.Items.Add("Все");
            else listBox4.Items.Add("Не выбрано");
        }

        private void checkBox5_CheckedChanged(object sender, EventArgs e)
        {
            listBox3.Items.Clear();
            if (checkBox5.Checked) listBox3.Items.Add("Все");
            else listBox3.Items.Add("Не выбрано");
        }
    }
}
